# import pandas
#
# def read_excel(filepaath):
#     getdata = []
#     dy = pandas.read_excel(filepaath)
#     for index, row in dy.iterrows():
#         getdata.append([row['username'], row['password']])
#
#     return getdata
#
#
# print(read_excel('./A3mall_login.xlsx'))


from openpyxl import load_workbook
class read_excel():
    def __init__(self, filepath):
        self.filepath = filepath
        self.wb = load_workbook(filepath)
    def getdata_excel(self,shettname):
        sheet_name = self.wb[shettname]
        datalist = []
        for item in sheet_name:
            list = []
            list.append(item[0].value)
            list.append(item[1].value)
            list.append(item[2].value)
            list.append(item[9].value)
            list.append(item[11].value)
            list.append(item[12].value)
            datalist.append(list)
        datalist.pop(0)
        return datalist

    # 根据行数、列数写入数据
    def set_excel(self, shettname, row, col, set_value):
        sheet_name = self.wb[shettname]
        sheet_name.cell(row, col, str(set_value))
        self.wb.save(self.filepath)

    # 根据列名、行名写入数据
    def write(self,sheetname, row_name, column_name, write_value):
        sheet_name = self.wb[sheetname]
        # row_count = sheet_name.max_row
        # print('***'+str(row_count))
        # print(sheet_name[3][2].value)
        # 获取第一行的行名
        row1 = [cell.value for cell in sheet_name[1]]
        # 获取第一列的列名
        column1 = [cell.value for col in sheet_name.iter_cols(min_col=1, max_col=1) for cell in col]
        # print('******')
        # print(row1)
        # print(column1)
        # print('******')
        # 检查行名是否存在   如果 row_name 不存在于 row1 中，打印一条错误消息并返回，不执行后续操作
        if row_name in row1:
            # 第一行的行名对应添加方法cell中的column列数
            col_index = row1.index(row_name) + 1  # 加1时因为第一列是标题列
        else:
            print(f"Row name '{row_name}' not found.")
            return
        # 检查列名是否存在   如果 column_name 不存在于 column1[0] 中，打印一条错误消息并返回，不执行后续操作
        if column_name in column1:
            # 第一列的列名对应添加方法cell中的row行数
            row_index = column1.index(column_name) + 1  # 加1是因为第一行是标题行
        else:
            print(f"Column name '{column_name}' not found.")
            return
        # 写入数据
        sheet_name.cell(row=row_index, column=col_index, value=str(write_value))
        self.wb.save(self.filepath)
        print(f"Data written successfully at {sheetname}!{row_name}!{column_name}")





# filepath = r'./movie.xlsx'
# tt = read_excel(filepath)
# print(tt.getdata_excel('Sheet2'))
# # print(tt.set_excel('Sheet3', 2, 2, '小高'))
# tt.write('Sheet2', '小高', 'Alice', 100)
# print(tt.getdata_excel('Sheet2'))


# class read_excel():
#     def __init__(self, filepath):
#         self.filepath = filepath
#         self.wb = load_workbook(filepath)
#     def getdata_excel(self,sheetname):
#         sheet_name = self.wb[sheetname]
#         datalist = []
#         # print(sheet_name['A1'].value)
#         for item in sheet_name:
#             list = []
#             list.append(item[0].value)
#             list.append(item[1].value)
#             list.append(item[2].value)
#             # list.append(item[9].value)
#             # list.append(item[11].value)
#             # list.append(item[12].value)
#             datalist.append(list)
#         datalist.pop(0)
#         return datalist
#
#     # 根据行数、列数写入数据
#     def set_excel(self, sheetname, row, col, set_value):
#         sheet_name = self.wb[sheetname]
#         sheet_name.cell(row, col, str(set_value))
#         self.wb.save(self.filepath)
#
#     # 根据列名、行名写入数据
#     def write(self,sheetname, row_name, column_name, write_value):
#         sheet_name = self.wb[sheetname]
#         # 获取第一行的行名
#         row1 = [cell.value for cell in sheet_name[1]]
#         # 获取第一列的列名
#         column1 = []
#         for i in range(2, len(row1)+2):
#             column1.append(sheet_name[i][0].value)
#         print(row1)
#         print(column1)
#         # 使用双重判断来检查行名和列名是否存在，并获取相对应的下标
#         if row_name in row1:
#             row_index = row1.index(row_name) + 2  # 加2是因为索引从0开始，且第一行是标题行
#         else:
#             print(f"Row name '{row_name}' not found.")
#             return
#
#         if column_name in column1:
#             col_index = column1.index(column_name) + 1  # 加1是因为第一列是标题列
#         else:
#             print(f"Column name '{column_name}' not found.")
#             return
#
#         # 写入数据
#         sheet_name.cell(row=row_index, column=col_index, value=str(write_value))
#         self.wb.save(self.filepath)
#         print(f"Data written successfully at {sheetname}!{row_name}!{column_name}")
#
#
#
# filepath = r'./login.xlsx'
# tt = read_excel(filepath)
# print(tt.getdata_excel('Sheet3'))
# # print(tt.set_excel('Sheet3', 2, 2, '小高'))
# tt.write('Sheet3', '小高', 'Alice', 100)
# print(tt.getdata_excel('Sheet3'))







